package org.yoao.crm.dao;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.yoao.crm.DBManager.DBManager;
import org.yoao.crm.bean.Company;
import org.yoao.crm.bean.StaticBean;

/**
 * @author yxwonline.com
 * @description
 * @date 2010-7-31 下午01:46:20
 */
public class CompanyDAO extends DBManager {
	private static final Logger log = Logger.getLogger(CompanyDAO.class);

	private Company findSingle(Map data) throws Exception{
		Company company = null;
		if(data != null && !data.isEmpty()){
			company = new Company();
			company.setId(Integer.parseInt(data.get("ID").toString()));
			company.setCompanyId((String)data.get("COMPANYID"));
			company.setCompanyName((String)data.get("COMPANYNAME"));
		}
		return company;
	}

	public Company findCompanyByCompanyId(String companyId) throws Exception {
		String sql = "select * from company where companyId='"+companyId+"'";
		return findSingle(querySingleRow(sql));
	}

	public Company findById(String id) throws Exception {
		String sql = "select * from company where id="+id;
		return findSingle(querySingleRow(sql));
	}

	public int getDataCount(Map map) throws Exception{
		String sql = "select count(id) as userCount from company";
		String where = " where ";
		if(map != null && !map.isEmpty()){
			Iterator iter = map.keySet().iterator();
			while(iter.hasNext()){
				String key = iter.next().toString();
				String value = map.get(key).toString();
				where = where + key + "='" + value + "' and ";
			}
		}
		if(where.endsWith(" and ")) where = where.substring(0, where.length()-4);
		if(where.equals(" where ")) where = "";
		sql += where;
		String result = querySingleValue(sql);
		if(result == null || result.trim().equals("")) result = "0";
		return Integer.parseInt(result);
	}

	public List findAll() throws Exception{
		String sql = "select ID, COMPANYID, COMPANYNAME from company";
		return find(sql);
	}

	public List find(int startRow, int endRow, Map map) throws Exception{
		String sql = "select top "+endRow+" company.id, company.companyId, company.companyName from company " +
					 "where company.id not in(";
		String filterSql = "select top "+startRow+" c2.id from company as c2 ";

		String where = "";
		String fwhere = "";
		if(map != null && !map.isEmpty()){
			Iterator iter = map.keySet().iterator();
			while(iter.hasNext()){
				String key = iter.next().toString();
				String value = map.get(key).toString();
				where = where + "company." + key + "='" + value + "' and ";
				fwhere = fwhere + "c2." + key + "='" + value + "' and ";
			}
		}
		if(where.endsWith(" and ")) where = where.substring(0, where.length()-4);
		if(where.equals("")) where = null;
		if(fwhere.endsWith(" and ")) fwhere = fwhere.substring(0, fwhere.length()-4);
		if(fwhere.equals("")) fwhere = null;

		if(where == null){
			fwhere = " order by c2.id desc";
			sql = sql + filterSql + fwhere + ") order by company.id desc";
		}else{
			filterSql = filterSql + " where " + fwhere + " order by c2.id desc";
			sql = sql + filterSql + ") and " + where + " order by company.id desc";
		}
		return find(sql);
	}

	private List find(String sql) throws Exception{
		List list = queryMultiColumn(sql);
		List result = null;
		if(list != null && !list.isEmpty()){
			result = new ArrayList();
			for(int i=0;i<list.size();i++){
				Map map = (Map)list.get(i);
				Company company = findSingle(map);
				if(company == null) company = new Company();
				result.add(company);
			}
		}
		return result;
	}

	public int insert(Company company) throws Exception{
		String sql = "insert into company(companyId, companyName)values('"+
					 company.getCompanyId()+"', '"+company.getCompanyName()+"')";
		int result = -1;
		if(update(sql) > -1){
			sql = "select id from company where companyId='"+company.getCompanyId()+"'";
			String r = querySingleValue(sql);
			if(r != null && !r.trim().equals("")){
				result = Integer.parseInt(r);
			}
		}
		return result;
	}

	public int modify(Company company) throws Exception{
		String sql = "update company set companyName='"+company.getCompanyName()+
					 "' where companyId='"+company.getCompanyId()+"'";
		return update(sql);
	}

	public int delete(String id) throws Exception{
		String sql = "delete from company where id="+id;
		return update(sql);
	}

	public synchronized String create(){
		try {
			log.info("开始初始化表company...");
			String sql = "SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[company]');";
			List list = this.queryMultiColumn(sql);
			if(list == null){
				log.warn("系统检测失败。\n原因:\n  1.数据库非正常运行;\n  2.帐户没权限不足;\n  3.数据库连接异常。");
				return "系统检测失败。\n原因:\n  1.数据库非正常运行;\n  2.帐户没权限不足;\n  3.数据库连接异常。";
			}
			if(list.isEmpty()){
				log.info("表company不存在，开始创建...");
				sql = "/****** 对象:  Table [dbo].[company]    脚本日期: 06/24/2009 17:40:16 ******/ " +
					  "SET ANSI_NULLS ON;" +
					  "SET QUOTED_IDENTIFIER ON;" +
					  "SET ANSI_PADDING ON;" +
					  "CREATE TABLE [dbo].[company](" +
					  		    "[id] [int] IDENTITY(1,1) NOT NULL," +
					  			"[companyId] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL," +
					  			"[companyName] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL," +
					  "CONSTRAINT [PK_company] PRIMARY KEY CLUSTERED" +
					  "(" +
					  			"[id] ASC" +
					  ")WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]" +
					  ") ON [PRIMARY];" +
					  "SET ANSI_PADDING OFF;";

				if(this.update(sql) < 0){
					log.warn("表company初始化失败!");
					return "表company初始化失败!";
				}
				log.info("表company创建成功!");
				log.info("给表company初始化数据!");
				sql = "insert into company(companyId, companyName) values('"+StaticBean.CODE_HQ+"', '上海游傲文化有限公司')";
				update(sql);
				return "表company创建成功!";
			}
			else{
				log.info("表company已存在!");
				return "表company已存在!";
			}
		}
		catch (Exception e) {
			log.warn("表company初始化失败!");
			e.printStackTrace();
			return "表company初始化失败!" + e.toString();
		}
	}
}